const express = require('express');
const router = express.Router();
const mysql = require('../services/mysql');

// 基础统计数据API
router.get('/api/stats', async (req, res) => {
    try {
        const { symbol, interval, range } = req.query;
        
        // 参数验证
        if (!symbol || !interval) {
            return res.status(400).json({ error: '缺少必要参数' });
        }
        
        // 根据时间范围获取数据
        const stats = await getBasicStats(symbol, interval, range);
        
        res.json(stats);
    } catch (error) {
        console.error('获取统计数据出错:', error);
        res.status(500).json({ error: '获取统计数据失败' });
    }
});

// 增强版统计数据API
router.get('/api/enhanced_stats', async (req, res) => {
    try {
        const { symbol, interval, range } = req.query;
        
        // 参数验证
        if (!symbol || !interval || !range) {
            return res.status(400).json({ error: '缺少必要参数' });
        }
        
        // 解析时间范围参数
        let startTime = new Date();
        switch(range) {
            case '3m':
                startTime.setMonth(startTime.getMonth() - 3);
                break;
            case '6m':
                startTime.setMonth(startTime.getMonth() - 6);
                break;
            case '1y':
                startTime.setFullYear(startTime.getFullYear() - 1);
                break;
            case '2y':
                startTime.setFullYear(startTime.getFullYear() - 2);
                break;
            case 'all':
                startTime = new Date(2018, 0, 1); // 币安数据起始时间
                break;
            default:
                startTime.setMonth(startTime.getMonth() - 3);
        }
        
        // 转换为时间戳
        const startTimeMs = startTime.getTime();
        
        // 直接从数据库获取增强统计数据，不使用缓存
        console.log(`直接从数据库获取增强统计数据: ${symbol}, ${interval}, 从 ${new Date(startTimeMs).toLocaleString()}`);
        const enhancedStats = await getEnhancedStats(symbol, interval, startTimeMs);
        
        res.json(enhancedStats);
    } catch (error) {
        console.error('获取增强版统计数据出错:', error);
        res.status(500).json({ error: '获取统计数据失败' });
    }
});

// 获取基础统计数据
async function getBasicStats(symbol, interval, range) {
    try {
        // 这里实现从数据库获取基础统计数据的逻辑
        // 由于涉及复杂查询，暂时返回模拟数据
        return {
            symbol,
            interval,
            range,
            data: {
                avgPrice: 45000,
                priceChange: 1.5,
                volume: 123456789,
                // 更多统计数据...
            }
        };
    } catch (error) {
        console.error('获取基础统计数据出错:', error);
        throw error;
    }
}

// 获取增强统计数据
async function getEnhancedStats(symbol, interval, startTimeMs) {
    try {
        // 获取连接
        const connection = await mysql.getConnection();
        
        try {
            // 1. 计算总K线数量
            const [totalResults] = await connection.query(
                `SELECT COUNT(*) as total FROM kline_${interval}_enhanced 
                 WHERE symbol = ? AND open_time >= ?`,
                [symbol, startTimeMs]
            );
            const totalCandles = totalResults[0].total;
            
            // 2. 按小时统计
            const [hourlyStats] = await connection.query(
                `SELECT 
                    hour_of_day,
                    COUNT(*) as count,
                    AVG((close_price - open_price) / open_price * 100) as avg_return,
                    AVG(volume) as avg_volume,
                    SUM(CASE WHEN close_price > open_price THEN 1 ELSE 0 END) / COUNT(*) * 100 as bullish_percent
                 FROM kline_${interval}_enhanced
                 WHERE symbol = ? AND open_time >= ?
                 GROUP BY hour_of_day
                 ORDER BY hour_of_day`,
                [symbol, startTimeMs]
            );
            
            // 3. 按周内日统计
            const [weekdayStats] = await connection.query(
                `SELECT 
                    day_of_week,
                    COUNT(*) as count,
                    AVG((close_price - open_price) / open_price * 100) as avg_return,
                    AVG((high_price - low_price) / open_price * 100) as avg_volatility,
                    SUM(CASE WHEN close_price > open_price THEN 1 ELSE 0 END) / COUNT(*) * 100 as bullish_percent
                 FROM kline_${interval}_enhanced
                 WHERE symbol = ? AND open_time >= ?
                 GROUP BY day_of_week
                 ORDER BY day_of_week`,
                [symbol, startTimeMs]
            );
            
            // 4. 按周末和工作日统计
            const [weekendStats] = await connection.query(
                `SELECT 
                    is_weekend,
                    COUNT(*) as count,
                    AVG((close_price - open_price) / open_price * 100) as avg_return,
                    AVG(volume) as avg_volume,
                    AVG((high_price - low_price) / open_price * 100) as avg_volatility,
                    SUM(CASE WHEN close_price > open_price THEN 1 ELSE 0 END) / COUNT(*) * 100 as bullish_percent
                 FROM kline_${interval}_enhanced
                 WHERE symbol = ? AND open_time >= ?
                 GROUP BY is_weekend
                 ORDER BY is_weekend`,
                [symbol, startTimeMs]
            );
            
            // 5. 按美股交易时段统计
            const [usMarketStats] = await connection.query(
                `SELECT 
                    is_us_market_open,
                    COUNT(*) as count,
                    AVG((close_price - open_price) / open_price * 100) as avg_return,
                    AVG(volume) as avg_volume,
                    AVG((high_price - low_price) / open_price * 100) as avg_volatility,
                    SUM(CASE WHEN close_price > open_price THEN 1 ELSE 0 END) / COUNT(*) * 100 as bullish_percent
                 FROM kline_${interval}_enhanced
                 WHERE symbol = ? AND open_time >= ?
                 GROUP BY is_us_market_open
                 ORDER BY is_us_market_open`,
                [symbol, startTimeMs]
            );
            
            // 6. K线类型分布统计
            const [candleTypeStats] = await connection.query(
                `SELECT 
                    CASE 
                        WHEN close_price > open_price THEN 'bullish'
                        WHEN close_price < open_price THEN 'bearish'
                        ELSE 'doji'
                    END as candle_type,
                    COUNT(*) as count,
                    COUNT(*) / ? * 100 as percentage,
                    AVG(ABS((close_price - open_price) / open_price * 100)) as avg_return,
                    AVG(volume) as avg_volume
                 FROM kline_${interval}_enhanced
                 WHERE symbol = ? AND open_time >= ?
                 GROUP BY candle_type`,
                [totalCandles, symbol, startTimeMs]
            );
            
            // 7. 连续涨跌统计
            // 这需要一个复杂查询来识别连续上涨或下跌的模式
            const consecutiveStats = await getConsecutiveStats(symbol, interval, startTimeMs);
            
            // 返回完整的统计数据
            return {
                status: 'success',
                symbol,
                interval,
                timeRange: {
                    startTime: new Date(startTimeMs).toISOString(),
                    endTime: new Date().toISOString()
                },
                totalCandles,
                hourlyStats: formatHourlyStats(hourlyStats),
                weekdayStats: formatWeekdayStats(weekdayStats),
                weekendStats,
                usMarketStats,
                candleTypeStats,
                consecutiveStats
            };
        } finally {
            connection.release();
        }
    } catch (error) {
        console.error('获取增强统计数据出错:', error);
        throw error;
    }
}

// 格式化小时统计数据
function formatHourlyStats(hourlyStats) {
    // 确保所有小时都有数据
    const formattedStats = Array.from({ length: 24 }, (_, i) => {
        const found = hourlyStats.find(stat => stat.hour_of_day === i);
        if (found) {
            return {
                hour: i,
                count: found.count,
                avgReturn: parseFloat(found.avg_return.toFixed(2)),
                avgVolume: parseFloat(found.avg_volume.toFixed(2)),
                bullishPercent: parseFloat(found.bullish_percent.toFixed(2))
            };
        }
        return {
            hour: i,
            count: 0,
            avgReturn: 0,
            avgVolume: 0,
            bullishPercent: 0
        };
    });
    return formattedStats;
}

// 格式化星期几统计数据
function formatWeekdayStats(weekdayStats) {
    // 确保所有星期都有数据
    const formattedStats = Array.from({ length: 7 }, (_, i) => {
        const found = weekdayStats.find(stat => stat.day_of_week === i);
        if (found) {
            return {
                weekday: i,
                count: found.count,
                avgReturn: parseFloat(found.avg_return.toFixed(2)),
                avgVolatility: parseFloat(found.avg_volatility.toFixed(2)),
                bullishPercent: parseFloat(found.bullish_percent.toFixed(2))
            };
        }
        return {
            weekday: i,
            count: 0,
            avgReturn: 0,
            avgVolatility: 0,
            bullishPercent: 0
        };
    });
    return formattedStats;
}

// 获取连续涨跌统计
async function getConsecutiveStats(symbol, interval, startTimeMs) {
    try {
        const tableName = `kline_${interval}_enhanced`;
        const startDate = new Date(startTimeMs).toISOString().split('T')[0];
        
        // 计算连续涨跌概率统计
        const statsQuery = `
            WITH consecutive_counts AS (
                SELECT 
                    SUM(CASE WHEN close_price > open_price THEN 1 ELSE 0 END) as up_count,
                    SUM(CASE WHEN close_price < open_price THEN 1 ELSE 0 END) as down_count,
                    COUNT(*) as total_count
                FROM ${tableName}
                WHERE symbol = ? AND open_time >= ?
            ),
            consecutive_runs AS (
                SELECT 
                    a.open_time,
                    a.close_time,
                    a.symbol,
                    CASE WHEN a.close_price > a.open_price THEN 'up' ELSE 'down' END AS direction,
                    a.open_price,
                    a.close_price,
                    COUNT(*) OVER (PARTITION BY grp) AS consecutive_count,
                    FIRST_VALUE(a.open_time) OVER (PARTITION BY grp ORDER BY a.open_time) AS start_time,
                    FIRST_VALUE(a.open_price) OVER (PARTITION BY grp ORDER BY a.open_time) AS start_price,
                    LAST_VALUE(a.close_time) OVER (PARTITION BY grp ORDER BY a.open_time 
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS end_time,
                    LAST_VALUE(a.close_price) OVER (PARTITION BY grp ORDER BY a.open_time 
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS end_price
                FROM (
                    SELECT 
                        *,
                        SUM(CASE 
                            WHEN (close_price > open_price) != LAG(close_price > open_price, 1, NULL) OVER (ORDER BY open_time) 
                                OR LAG(close_price > open_price, 1, NULL) OVER (ORDER BY open_time) IS NULL 
                            THEN 1 
                            ELSE 0 
                        END) OVER (ORDER BY open_time) AS grp
                    FROM ${tableName}
                    WHERE symbol = ? AND open_time >= ?
                    ORDER BY open_time
                ) a
                WHERE (a.close_price > a.open_price OR a.close_price < a.open_price)
            ),
            consecutive_stats AS (
                SELECT 
                    consecutive_count,
                    direction,
                    COUNT(*) as count,
                    AVG((end_price - start_price) / start_price * 100) as avg_return
                FROM (
                    SELECT 
                        consecutive_count,
                        direction,
                        start_time,
                        end_time,
                        start_price,
                        end_price
                    FROM consecutive_runs
                    GROUP BY grp
                    HAVING consecutive_count >= 2
                ) sub
                GROUP BY consecutive_count, direction
                ORDER BY consecutive_count
            )
            SELECT 
                cs.consecutive_count,
                SUM(CASE WHEN cs.direction = 'up' THEN cs.count ELSE 0 END) as up_count,
                SUM(CASE WHEN cs.direction = 'up' THEN cs.avg_return ELSE 0 END) as avg_up_return,
                SUM(CASE WHEN cs.direction = 'down' THEN cs.count ELSE 0 END) as down_count,
                SUM(CASE WHEN cs.direction = 'down' THEN cs.avg_return ELSE 0 END) as avg_down_return,
                SUM(cs.count) as total_count,
                ROUND(100.0 * SUM(cs.count) / cc.total_count, 2) as frequency
            FROM consecutive_stats cs, consecutive_counts cc
            GROUP BY cs.consecutive_count
            ORDER BY cs.consecutive_count;
        `;
        
        // 查询最近的连续涨跌记录
        const recentRunsQuery = `
            WITH consecutive_runs AS (
                SELECT 
                    a.open_time,
                    a.close_time,
                    a.symbol,
                    CASE WHEN a.close_price > a.open_price THEN 'up' ELSE 'down' END AS direction,
                    a.open_price,
                    a.close_price,
                    COUNT(*) OVER (PARTITION BY grp) AS consecutive_count,
                    FIRST_VALUE(a.open_time) OVER (PARTITION BY grp ORDER BY a.open_time) AS start_time,
                    FIRST_VALUE(a.open_price) OVER (PARTITION BY grp ORDER BY a.open_time) AS start_price,
                    LAST_VALUE(a.close_time) OVER (PARTITION BY grp ORDER BY a.open_time 
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS end_time,
                    LAST_VALUE(a.close_price) OVER (PARTITION BY grp ORDER BY a.open_time 
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS end_price
                FROM (
                    SELECT 
                        *,
                        SUM(CASE 
                            WHEN (close_price > open_price) != LAG(close_price > open_price, 1, NULL) OVER (ORDER BY open_time) 
                                OR LAG(close_price > open_price, 1, NULL) OVER (ORDER BY open_time) IS NULL 
                            THEN 1 
                            ELSE 0 
                        END) OVER (ORDER BY open_time) AS grp
                    FROM ${tableName}
                    WHERE symbol = ? AND open_time >= ?
                    ORDER BY open_time
                ) a
                WHERE (a.close_price > a.open_price OR a.close_price < a.open_price)
            )
            SELECT 
                direction as type,
                consecutive_count as count,
                start_time as startTime,
                end_time as endTime,
                start_price as startPrice,
                end_price as endPrice,
                ROUND((end_price - start_price) / start_price * 100, 2) as pctChange
            FROM (
                SELECT 
                    direction,
                    consecutive_count,
                    start_time,
                    end_time,
                    start_price,
                    end_price,
                    ROW_NUMBER() OVER (PARTITION BY grp) as rn
                FROM consecutive_runs
                GROUP BY grp
                HAVING consecutive_count >= 3
                ORDER BY end_time DESC
            ) sub
            WHERE rn = 1
            ORDER BY endTime DESC;
        `;
        
        const connection = await mysql.getConnection();
        
        try {
            const [statsResults] = await connection.query(statsQuery, [symbol, startDate, symbol, startDate]);
            const [recentRuns] = await connection.query(recentRunsQuery, [symbol, startDate]);
            
            return {
                stats: statsResults,
                recentRuns: recentRuns
            };
        } finally {
            connection.release();
        }
    } catch (error) {
        console.error('获取连续涨跌统计出错:', error);
        throw error;
    }
}

module.exports = router; 